package by.bsu.touragency.db.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.log4j.Logger;

import by.bsu.touragency.db.beans.BookedTour;
import by.bsu.touragency.db.beans.Person;
import by.bsu.touragency.db.beans.Tour;
import by.bsu.touragency.db.connection.DBConnector;

public class BookedTourDAO extends AbstractDAO {

	public static final String SQL_BOOKEDTOUR_CREATE = " INSERT INTO tourAgencyDB.bookedTour (tourId, clientId) VALUES (? , ?);";
	public static final String SQL_BOOKEDTOUR_CREATE_GET_ID = "SELECT * FROM tourAgencyDB.bookedTour WHERE tourId = ? AND clientId = ? ;";
	public static final String SQL_BOOKEDTOUR_IS_EXIST = " SELECT COUNT(*) AS rowcount FROM tourAgencyDB.bookedTour WHERE tourId = ? AND clientId = ? ;";
	public static final String SQL_BOOKEDTOUR_READ_BY_ID = "SELECT * FROM tourAgencyDB.bookedTour WHERE (id= ?);";
	public static final String SQL_BOOKEDTOUR_READ = "SELECT * FROM tourAgencyDB.bookedTour;";
	public static final String SQL_BOOKEDTOUR_READ_BY_CLIENT = "SELECT * FROM tourAgencyDB.bookedTour WHERE (clientId= ?);";
	public static final String SQL_BOOKEDTOUR_READ_BY_TOUR_CLIENT = "SELECT * FROM tourAgencyDB.bookedTour WHERE (clientId= ?) AND (tourId= ?);";
	public static final String SQL_BOOKEDTOUR_READ_BY_TOUR = "SELECT * FROM tourAgencyDB.bookedTour WHERE (tourId= ?);";
	public static final String SQL_BOOKEDTOUR_DELETE = "DELETE FROM tourAgencyDB.bookedTour WHERE id = ?";

	private static Logger logger = Logger.getLogger("log");

	public boolean isExist(BookedTour bt) {
		try {// 1
			logger.info("Is tour booked?");
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_BOOKEDTOUR_IS_EXIST);
			int n;
			try {// 3
				statement.setLong(1, bt.getTour().getId());
				statement.setLong(2, bt.getPerson().getId());
				resultSet = statement.executeQuery();
				n = -2;
				resultSet.next();
				n = resultSet.getInt("rowcount");
			} finally {
			}// 3
			if (resultSet != null) {
				resultSet.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (n == 1) {
				logger.debug("This tour is booked");
				return true;
			} else {
				logger.debug("This tour is NOT booked");
				return false;
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
			return false;
		} catch (Exception e) {
			logger.error("Undefined exeption");
			return false;
		} finally {
			logger.debug("Close connection");
			close();
		}

	}

	// C
	public BookedTour create(BookedTour bt) {
		try {
			logger.info("Book tour");
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_BOOKEDTOUR_CREATE);
			statement.setLong(1, bt.getTour().getId());
			statement.setLong(2, bt.getPerson().getId());
			statement.executeUpdate();
			statement = connector
					.getPreparedStatement(SQL_BOOKEDTOUR_CREATE_GET_ID);
			statement.setLong(1, bt.getTour().getId());
			statement.setLong(2, bt.getPerson().getId());
			resultSet = statement.executeQuery();

			if (resultSet.next()) {
				bt.setId(resultSet.getInt("id"));
			}

			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}

		return bt;
	}

	// R
	public BookedTour readById(int id) {
		BookedTour bt = new BookedTour();
		try {
			logger.info("Read booked tour by id");
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_BOOKEDTOUR_READ_BY_ID);
			statement.setInt(1, bt.getId());
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				bt.setId(resultSet.getInt("id"));

				Person c = new Person();
				PersonDAO cd = new PersonDAO();
				c = cd.readById(resultSet.getInt("clientId"));
				bt.setClient(c);

				Tour t = new Tour();
				TourDAO td = new TourDAO();
				t = td.readById(resultSet.getInt("tourId"));
				bt.setTour(t);
			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
		return bt;
	}

	public BookedTour readByTourClient(int idClient, int idTour) {
		BookedTour bt = new BookedTour();
		try {
			logger.info("Read booked tour by id");
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_BOOKEDTOUR_READ_BY_TOUR_CLIENT);
			statement.setInt(1, idClient);
			statement.setInt(2, idTour);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				bt.setId(resultSet.getInt("id"));
			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
		return bt;
	}

	public ArrayList<Tour> readByClient(Person client) {
		ArrayList<Tour> ab = new ArrayList<Tour>();
		try {
			logger.info("Read all boked tours of this client");
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_BOOKEDTOUR_READ_BY_CLIENT);
			statement.setInt(1, client.getId());
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				Tour t = new Tour();
				TourDAO td = new TourDAO();
				t = td.readById(resultSet.getInt("tourId"));
				ab.add(t);
			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
		return ab;
	}

	public ArrayList<Person> readByTour(Tour tour) {
		ArrayList<Person> ab = new ArrayList<Person>();
		try {
			logger.info("Read all client of this tour.");
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_BOOKEDTOUR_READ_BY_TOUR);
			statement.setInt(1, tour.getId());
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				Person c = new Person();
				PersonDAO cd = new PersonDAO();
				c = cd.readById(resultSet.getInt("clientId"));
				ab.add(c);
			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
		return ab;
	}

	// D
	public void deleteById(int id) {
		try {
			logger.info("Unbook tour by booked tour id");
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_BOOKEDTOUR_DELETE);
			statement.setInt(1, id);
			statement.executeUpdate();
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
	}
}
